package com.cm386.final_project_timezonealert.dao;




import java.util.ArrayList;
import java.util.List;
import java.util.TimeZone;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.cm386.final_project_timezonealert.model.City;
import com.cm386.final_project_timezonealert.model.Region;

public class DBService extends SQLiteOpenHelper{
	
	private static final String DB_NAME = "TimeZoneAlertProject.db";
	private static final int VERSION    = 100;
	
	private static DBService dbService  = null;
	
	public static DBService getInstance(Context context){
		if (dbService == null)
			dbService = new DBService(context);
		
		return dbService;
	}
	
	public DBService(Context context){
		this(context, DB_NAME, null, VERSION);
		dbService = this;
	}
	
	public DBService(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE select_city (_ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, TIMEZONEID TEXT NOT NULL UNIQUE, BELONGREGION TEXT NOT NULL);");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	public void insertSelectedCity(City selectedCity){
		if (selectedCity != null){
			SQLiteDatabase wdb = this.getWritableDatabase();
			SQLiteStatement stmt = wdb.compileStatement("INSERT INTO select_city (NAME, TIMEZONEID, BELONGREGION) VALUES (?, ?, ?)");
			stmt.bindString(1, selectedCity.getName());
			stmt.bindString(2, selectedCity.getTimeZoneId());
			stmt.bindString(3, selectedCity.getBelongRegion());
			stmt.execute();
			stmt.close();
			wdb.close();
		} else {
			throw new NullPointerException("selectedCity is null when execute insertCity(selectedCity)");
		}
	}
	
	public void deleteSelectedCity(City selectedCity){
		if (selectedCity != null){
			deleteSelectedCityByTimeZoneId(selectedCity.getTimeZoneId());
		} else {
			throw new NullPointerException("selectedCity is null when execute deleteSelectedCity(selectedCity)");
		}
	}
	
	public void deleteSelectedCityByTimeZoneId(String timeZoneId){
		if (timeZoneId != null && timeZoneId.trim().length() > 0){
			SQLiteDatabase wdb = this.getWritableDatabase();
			SQLiteStatement stmt = wdb.compileStatement("DELETE FROM select_city WHERE TIMEZONEID = ?");
			stmt.bindString(1, timeZoneId);
			stmt.execute();
			stmt.close();
			wdb.close();
		} else {
			throw new NullPointerException("TimeZoneId is null when execute deleteCityByTimeZoneId(TimeZoneId)");
		}
	}
	
	public void deleteAllSelectedCity(){
		SQLiteDatabase wdb = this.getWritableDatabase();
		SQLiteStatement stmt = wdb.compileStatement("DELETE FROM select_city ");
		stmt.execute();
		stmt.close();
		wdb.close();
	}
	
	public List<City> fetchAllCity(){
		List<City> selectedCities = new ArrayList<City>();
		SQLiteDatabase rdb 		  = this.getReadableDatabase();
		Cursor cursor = rdb.rawQuery("SELECT * FROM select_city ORDER BY _ID;", null);
		if (cursor.moveToFirst()){
			do{
				String name 	    = cursor.getString(cursor.getColumnIndex("NAME"));
				String timeZoneId   = cursor.getString(cursor.getColumnIndex("TIMEZONEID"));
				String belongRegion = cursor.getString(cursor.getColumnIndex("BELONGREGION"));
				Log.v("DB Data :", "NAME: " + name + " TIMEZONEID:" + timeZoneId + " BELONGREGION:" + belongRegion);
				TimeZone timeZoneObject = TimeZone.getTimeZone(timeZoneId);
				selectedCities.add(new City(name, timeZoneId, timeZoneObject, belongRegion));
			}while(cursor.moveToNext());
		}
		cursor.close();
		rdb.close();
		return selectedCities;
	}
	
	public List<City> fetchCityByRegion(Region region){
		List<City> cities = new ArrayList<City>();
		SQLiteDatabase rdb 		  = this.getReadableDatabase();
		Cursor cursor = rdb.rawQuery("SELECT * FROM select_city WHERE BELONGREGION = ? ORDER BY _ID;", new String[]{region.getName()});
		if (cursor.moveToFirst()){
			do{
				String name 	    = cursor.getString(cursor.getColumnIndex("NAME"));
				String timeZoneId   = cursor.getString(cursor.getColumnIndex("TIMEZONEID"));
				String belongRegion = cursor.getString(cursor.getColumnIndex("BELONGREGION"));
				Log.v("DB Data :", "NAME: " + name + " TIMEZONEID:" + timeZoneId + " BELONGREGION:" + belongRegion);
				TimeZone timeZoneObject = TimeZone.getTimeZone(timeZoneId);
				cities.add(new City(name, timeZoneId, timeZoneObject, belongRegion));
			}while(cursor.moveToNext());
		}
		cursor.close();
		rdb.close();
		return cities;
		
	}
}
